/**
 * 
 */
package com.thon.dao.project.impl;

import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.thon.dao.project.ProjectSupportDao;
import com.thon.dao.util.AbstractDao;
import com.thon.dao.util.Page;
import com.thon.entity.project.Product;
import com.thon.entity.project.Project;
import com.thon.entity.project.ProjectSupport;
import com.thon.entity.user.User;

/**
 * @file ProjectSupportDaoImpl.java
 * @author Zemo
 * @mail zemochen@gmail.com
 * @data 2013年10月11日 上午11:32:53
 * @description: TODO
 */
@Repository
public class ProjectSupportDaoImpl extends AbstractDao<ProjectSupport>
		implements ProjectSupportDao {

	public ProjectSupportDaoImpl() {
		super(ProjectSupport.class);
	}

	@Override
	public List<ProjectSupport> listProjectSupportsByProject(int projectId) {
		String queryString = "FROM ProjectSupport ps WHERE ps.project.id=?";
		return findList(queryString, null, projectId);
	}

	@Override
	public List<ProjectSupport> listProjectSupportsByUser(int userId) {

		String queryString = "FROM ProjectSupport ps WHERE ps.user.id=?";
		return findList(queryString, null, userId);
	}

	@Override
	public Long countSupports(int projectId) {

		String queryString = "SELECT COUNT(*) FROM ProjectSupport ps WHERE ps.project.id = ?";
		return count(queryString, projectId);
	}

	@Override
	public Long sumFundCurrent(Integer projectId) {

		String sqlString = "SELECT SUM(b.price) FROM ym_project_supports  a  LEFT JOIN (ym_products b) ON a.product_id = b.id  WHERE a.project_id = ?";
		return countBySql(sqlString, projectId);
	}
	
	
	@Override
	public Long sumSaleCurrent(Integer projectId) {

		String sqlString = "SELECT SUM(b.price*a.product_amount) FROM ym_project_supports  a  LEFT JOIN (ym_products b) ON a.product_id = b.id  WHERE a.project_id = ?";
		return countBySql(sqlString, projectId);
	}
	
	@Override
	public Long sumFundByUser(Integer projectId, int userId) {
		String sqlString = "SELECT SUM(b.price) FROM ym_project_supports  a  LEFT JOIN (ym_products b) ON a.product_id = b.id  WHERE a.project_id = ? AND a.user_id=?";
		return countBySql(sqlString, projectId,userId);
		
	}

	@Override
	public Page<ProjectSupport> findSupportsById(Page<ProjectSupport> page,
			int projectId) {
		String queryString = "FROM ProjectSupport ps WHERE ps.project.id = ?";
		return findPage(page, queryString, projectId);
	}

	@Override
	public Page<ProjectSupport> findSupportsByUser(Page<ProjectSupport> page,
			int userId, String start, String end) {
		String sqlQueryString = "";
		if (start != null && end != null) {
			sqlQueryString = "SELECT * FROM ym_project_supports ps where ps.user_id=? and ps.created_date>=? and ps.created_date<=? ORDER BY ps.id DESC";
			return findPageBySql(page, sqlQueryString, null, userId, start, end);
		} else {
			sqlQueryString = "SELECT * FROM ym_project_supports ps where ps.user_id=? ORDER BY ps.id DESC";
			return findPageBySql(page, sqlQueryString, null, userId);
		}
	}
	@Override
	public Long countSupports(int projectId, int userId) {
		String sqlString="SELECT COUNT(*) FROM ym_project_supports ps WHERE ps.user_id=? AND ps.project_id=?";
		return countBySql(sqlString, userId, projectId);
	}
	@Override
	public List<ProjectSupport> getProjectSupport(int userId) {
		String queryString="FROM ProjectSupport ps WHERE ps.user.id=?";
		return findList(queryString,null, userId);
	}

	@Override
	public Page<ProjectSupport> findProjectSupport(Page page) {
		String queryString ="SELECT if(ISNULL(ps.id),u.id,(1000000-ps.project_id*10-u.id))as id,ps.project_id,u.id user_id,ps.product_id,ps.product_amount,ps.created_date,ps.status,ps.is_buy,ps.fund_code FROM ym_users u LEFT JOIN ym_project_supports ps ON u.id=ps.user_id GROUP BY u.id,ps.user_id,ps.project_id ORDER BY u.id";
		return findPageBySql(page, queryString,null);
	}

	@Override
	public List<ProjectSupport> listProjectSupportsByProjectId(Date registDateStart,Date comsumptionDateStart,Date registDateEnd,Date comsumptionDateEnd) {
		
			String queryString = "FROM ProjectSupport  p WHERE  p.user.registDate >= ? AND p.createdDate >= ? AND p.user.registDate <= ? AND p.createdDate <= ?";
			return findList(queryString, null,registDateStart,comsumptionDateStart,registDateEnd,comsumptionDateEnd);

		
	}

	@Override
	public Long countProductAmount(int projectId,int userId,int productId) {
		String sqlString = "SELECT SUM(product_amount) FROM ym_project_supports WHERE project_id = ? AND user_id = ? AND product_id = ?";
		return countBySql(sqlString, projectId,userId,productId);
				
	}
	
	@Override
	public Long countProductAmount(int projectId) {
		String sqlString = "SELECT SUM(product_amount) FROM ym_project_supports WHERE project_id = ?";
		return countBySql(sqlString, projectId);
				
	}

}
